-- @owner: opentestcase033
-- @date: 2023-06-07
-- @testpoint: 监控管理员提权测试：通过覆盖原先函数注入提权语句方式提权为sysadmin，覆盖失败，合理报错

--step1:创建测试用户并授权MONADMIN;expect:成功
create user u_privilege_0014 with password "Schina1234@";
ALTER USER u_privilege_0014 MONADMIN;

--step2:覆盖内置函数并嵌入提权语句;expect:覆盖失败
set role u_privilege_0014 password "Schina1234@";
CREATE OR REPLACE FUNCTION dbe_perf.get_summary_user_login()
 RETURNS SETOF dbe_perf.user_login
 LANGUAGE plpgsql
 SECURITY invoker NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
  ROW_DATA dbe_perf.user_login%ROWTYPE;
  ROW_NAME RECORD;
  QUERY_STR TEXT;
  QUERY_STR_NODES TEXT;
  statement text;
  BEGIN
    statement := 'ALTER USER u_privilege_0014 sysadmin';
    QUERY_STR_NODES := 'select * from dbe_perf.node_name';
    FOR ROW_NAME IN EXECUTE(QUERY_STR_NODES) LOOP
      QUERY_STR := 'SELECT * FROM dbe_perf.user_login';
      FOR ROW_DATA IN EXECUTE(QUERY_STR) LOOP
        RETURN NEXT ROW_DATA;
	  EXECUTE(statement);
      END LOOP;
    END LOOP;
    RETURN;
  END; $$;
/

--step3:清理环境;expect:成功
reset role;
drop user u_privilege_0014 cascade;
